package database;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import types.USER_TYPE;
import user.User;

/**
 * Utility Class DBHelper 
 * Allows easy access to the database 
 * @author warrenshenk
 */
public class DBHelper {
	Connection con;
	
//////////////////////////////////////////////////////////////////////////////////////////////////////////
// UTILITY METHODS
//////////////////////////////////////////////////////////////////////////////////////////////////////////
	
	/**
	 * Opens the Database Connection
	 */
	public void open() {
		con = null;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection("jdbc:mysql://academic-mysql.cc.gatech.edu/cs4400_Group56",
					"cs4400_Group56", "pJBv9wP_");
			if(!con.isClosed()) 
				System.out.println("Successfully connected to " +  "MySQL server using TCP/IP...");
		} 
		catch(Exception e) {
			System.err.println("Exception: " + e.getMessage());
        }
	}
	//----------------------------------------------------------------------------------------------------
	
	/** 
	 * Closes the Database Connection
	 */
	public void close() {
        try {
        	if (con != null) 
        		con.close();
        } 
        catch(SQLException e) {
        }

	}
	//---------------------------------------------------------------------------------------------------

	/**
	 * Queries the Database
	 * @param query String SQL Query
	 * @return ResultSet of results from SQL Query
	 */
	private ResultSet query(String query) {
		Statement statement;
		try {
			statement = con.createStatement();
			return statement.executeQuery(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	//----------------------------------------------------------------------------------------------------
	
	/**
	 * Executes an action the Database
	 * @param query String SQL Query
	 * @return boolean representing whether or not the statement was executed
	 */
	private boolean execute(String query) {
		Statement statement;
		try {
			statement = con.createStatement();
			return statement.execute(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	//----------------------------------------------------------------------------------------------------
	
//////////////////////////////////////////////////////////////////////////////////////////////////////////
// DATABASE METHODS
//////////////////////////////////////////////////////////////////////////////////////////////////////////
	
	/**
	 * Checks Login and Password 
	 * @param username
	 * @param password
	 * @return Boolean value representing whether or not the username/password combo exists
	 */
	public boolean isLoginValid (String username, String password) {
		ResultSet rs = null;
		rs = query("SELECT * FROM USER WHERE USERNAME = '" + username + "' AND PASSWORD = '" + password + "'");
		try {
			System.out.println(rs.first());
			return rs.first();
		} 
		catch (SQLException e) {
			System.out.println("ERR");
			return false;
		}
	}
	//----------------------------------------------------------------------------------------------------
	
	/**
	 * Checks to see if the Username is unique
	 * @param username
	 * @return Boolean Value representing whether or not the username is already in the database
	 */
	public boolean isUsernameUnique(String username) {
		ResultSet rs = null;
		rs = query("SELECT * FROM USER WHERE USERNAME = '" + username + "'");
		try {
			return !rs.first();
		} 
		catch (SQLException e) {
			return false;
		}
	}
	//----------------------------------------------------------------------------------------------------
	
	public void insertNewRegUser(String username, String password, String userType) {
		execute("INSERT INTO USER VALUES ('" + username + "', '" + password + "')" );
		execute("INSERT INTO REG_USER (REG_USERNAME, FAC_BIN) VALUES ('" + username +"' , 0)");
		if (userType.equals("Student")) 
			execute("INSERT INTO STUDENT (ST_USERNAME) VALUES  ('" + username +"')" );
		else if (userType.equals("Instructor"))
			execute("INSERT INTO FACULTY (INST_USERNAME) VALUES ('" + username +"')" );

	}
	//----------------------------------------------------------------------------------------------------
	
	public void updateRegUser(String userStr, String nameStr, String emailStr, String dobStr, String addrStr, String permAddrStr, char genderStr, String phoneStr ) {
		execute("UPDATE REG_USER SET ADDRESS = '" + addrStr + "' ," +
								"NAME = '" + nameStr  + "' ," +
								"EMAIL = '" + emailStr + "' , " +
								"DOB = '" + dobStr + "' ," +
								"PERM_ADDRESS = '" + permAddrStr + "' ," +
								"GENDER = '" + genderStr + "' ," + 
								"CONTACT_NUM = '" + phoneStr + "' " + 
						"WHERE REG_USERNAME = '" + userStr + "'");

	}
	//----------------------------------------------------------------------------------------------------\
	
	public void updateStudUser(String userStr, String majorStr, String degreeStr ) {
		execute("UPDATE STUDENT SET MAJOR = '" + majorStr + "' ," +
								"DEGREE = '" + degreeStr  + "' " +
							
						"WHERE ST_USERNAME = '" + userStr + "'");

	}
	//----------------------------------------------------------------------------------------------------
	
	public void updateInstUser(boolean infoExists, String userStr, String deptStr, String positionStr, String resIntStr) {
		System.out.println(deptStr);
		ResultSet rs = query("SELECT DEPARTMENT_ID FROM DEPT WHERE NAME = '" + deptStr + "'");
		String deptId = "";
		try {
			rs.first();
			deptId = (rs.getObject("DEPARTMENT_ID")).toString();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println(deptId);
		
		execute("UPDATE FACULTY SET POSITION = '" + positionStr + "' ," +
								"DEPT_ID = '"+ deptId + " ' " + 
							"WHERE INST_USERNAME = '" + userStr + "'");
		if (infoExists) {
			execute("UPDATE RESEARCH_INTERESTS SET INTEREST = '" +resIntStr + "' " +
											"WHERE RES_USERNAME = '" + userStr + "' ");
		}
		else {
			execute ("INSERT INTO RESEARCH_INTERESTS VALUES ( '" + userStr + "' , '" + resIntStr + "' )");
		}
		
		
		
		
	}
	//----------------------------------------------------------------------------------------------------


	public boolean informationIsSaved(User user) {
		ResultSet rs = null;
		rs = query("SELECT * FROM REG_USER WHERE REG_USERNAME = '" + user.getName() + "'");
		
		try {
			rs.first();
			if (rs.getObject("DOB") != null)
				return true;
			else
				return false;
		} 
		catch (SQLException e) { 
			e.printStackTrace();
			return false;
		}
		
	}
	//----------------------------------------------------------------------------------------------------

	/**
	 * Checks to see if username is a Student username & sets the user parameter's type to STUDENT if true
	 * @param user
	 * @return Boolean Value representing whether or not the username is already in the database
	 */
	public boolean isUserStudent(User user) {
		boolean isStudent;
		ResultSet rs = null;
		rs = query("SELECT * FROM STUDENT WHERE ST_USERNAME = '" + user.getName() + "'");
		try {
			isStudent = rs.first();
		} 
		catch (SQLException e) {
			return false;
		}
		if (isStudent) 
			user.setType(USER_TYPE.STUDENT);
		return isStudent;
	}
	//----------------------------------------------------------------------------------------------------

	/**
	 * Checks to see if the Username is a faculty username & sets the user parameter's type to INSTRUCTOR if true
	 * @param user
	 * @return Boolean Value representing whether or not the username is already in the database
	 */
	public boolean isUserFaculty(User user) {
		boolean isFac;
		ResultSet rs = null;
		rs = query("SELECT * FROM FACULTY WHERE INST_USERNAME = '" + user.getName() + "'");
		try {
			isFac = rs.first();
		} 
		catch (SQLException e) {
			return false;
		}
		if (isFac) {
			user.setType(USER_TYPE.INSTRUCTOR);
		}
		return isFac;
	}
	//----------------------------------------------------------------------------------------------------

	/**
	 * Checks to see if the Username is a faculty username & sets the user parameter's type to ADMIN if true
	 * @param username
	 * @return Boolean Value representing whether or not the username is already in the database
	 */
	public boolean isUserAdmin(User user) {
		boolean isAdmin;
		ResultSet rs = null;
		rs = query("SELECT * FROM ADMIN_USER WHERE ADMIN_USERNAME = '" + user.getName() + "'");
		try {
			isAdmin = rs.first();
		} 
		catch (SQLException e) {
			return false;
		}
		if (isAdmin)
			user.setType(USER_TYPE.ADMIN);
		return isAdmin;
	}
	//----------------------------------------------------------------------------------------------------
	

	
	
}
